﻿Imports System.Data.SqlClient
Imports System.Configuration
Imports SIGI.Common.BE

Public Class CommissionCalculationDAC

#Region "<< -SearchOperationTypes- >>"
    Public Shared Function SearchOperationTypes() As List(Of OperationBE)
        Dim wSqlConn As SqlConnection = Nothing
        Dim wSqlCmd As SqlCommand = Nothing
        Dim wReader As SqlDataReader = Nothing
        Dim wConnStr As String = String.Empty
        Dim wOperationList As List(Of OperationBE) = Nothing
        Dim wOperation As OperationBE = Nothing

        Try
            'Obtengo la cadena de conexión
            wConnStr = ConfigurationManager.ConnectionStrings("connString").ConnectionString
            'Creo la conexión a la BD
            wSqlConn = New SqlConnection(wConnStr)
            'Creo el comando
            wSqlCmd = New SqlCommand()
            wSqlCmd.Connection = wSqlConn
            wSqlCmd.CommandType = CommandType.StoredProcedure
            wSqlCmd.CommandText = "[dbo].[Operacion_s]"

            wSqlConn.Open()

            wOperationList = New List(Of OperationBE)
            wReader = wSqlCmd.ExecuteReader()
            While wReader.Read()
                wOperation = New OperationBE

                wOperation.Id = Convert.ToInt32(wReader("Tipo_Operacion"))
                wOperation.Description = Convert.ToString(wReader("Descripcion"))

                wOperationList.Add(wOperation)
            End While

            wSqlConn.Close()
        Catch ex As Exception
            Throw ex
        End Try

        Return wOperationList
    End Function
#End Region

#Region "<< -Search- >>"
    Public Shared Function Search() As List(Of CommissionCalculationBE)
        Dim wSqlConn As SqlConnection = Nothing
        Dim wSqlCmd As SqlCommand = Nothing
        Dim wReader As SqlDataReader = Nothing
        Dim wConnStr As String = String.Empty
        Dim wCommissionCalculationList As List(Of CommissionCalculationBE) = Nothing
        Dim wCommissionCalculation As CommissionCalculationBE = Nothing

        Try
            'Obtengo la cadena de conexión
            wConnStr = ConfigurationManager.ConnectionStrings("connString").ConnectionString
            'Creo la conexión a la BD
            wSqlConn = New SqlConnection(wConnStr)
            'Creo el comando
            wSqlCmd = New SqlCommand()
            wSqlCmd.Connection = wSqlConn
            wSqlCmd.CommandType = CommandType.StoredProcedure
            wSqlCmd.CommandText = "[dbo].[CalculoComision_s]"

            wSqlConn.Open()

            wCommissionCalculationList = New List(Of CommissionCalculationBE)
            wReader = wSqlCmd.ExecuteReader()
            While wReader.Read()
                wCommissionCalculation = New CommissionCalculationBE

                wCommissionCalculation.Operation.Id = Convert.ToInt32(wReader("Tipo_Operacion"))
                wCommissionCalculation.Operation.Description = Convert.ToString(wReader("Operacion")).Trim
                wCommissionCalculation.PropertyType.Id = Convert.ToInt32(wReader("Tipo_Inmueble"))
                wCommissionCalculation.PropertyType.Description = Convert.ToString(wReader("Inmueble")).Trim
                wCommissionCalculation.Mont = Convert.ToDecimal(wReader("Monto"))
                wCommissionCalculation.ValueScale = Convert.ToInt32(wReader("EscalaValor"))

                wCommissionCalculationList.Add(wCommissionCalculation)
            End While

            wSqlConn.Close()
        Catch ex As Exception
            Throw ex
        End Try

        Return wCommissionCalculationList
    End Function
#End Region

#Region "<< -Insert- >>"
    Public Shared Function Insert(pCommission As CommissionCalculationBE) As Integer
        Dim wSqlConn As SqlConnection = Nothing
        Dim wSqlCmd As SqlCommand = Nothing
        Dim wConnStr As String = String.Empty
        Dim wId As Integer = 0

        Try
            'Obtengo la cadena de conexión
            wConnStr = ConfigurationManager.ConnectionStrings("connString").ConnectionString
            'Creo la conexión a la BD
            wSqlConn = New SqlConnection(wConnStr)
            'Creo el comando
            wSqlCmd = New SqlCommand()
            wSqlCmd.Connection = wSqlConn
            wSqlCmd.CommandType = CommandType.StoredProcedure
            wSqlCmd.CommandText = "[dbo].[CalculoComision_i]"

            wSqlCmd.Parameters.Add("@TipoInmueble", SqlDbType.Int).Value = pCommission.PropertyType.Id.Value
            wSqlCmd.Parameters.Add("@TipoOperacion", SqlDbType.Int).Value = pCommission.Operation.Id.Value
            wSqlCmd.Parameters.Add("@Monto", SqlDbType.Decimal).Value = pCommission.Mont.Value
            wSqlCmd.Parameters.Add("@EscalaValor", SqlDbType.Int).Value = pCommission.ValueScale.Value

            wSqlConn.Open()

            wSqlCmd.ExecuteNonQuery()

            wSqlConn.Close()
        Catch ex As Exception
            Throw ex
        End Try

        Return wId
    End Function
#End Region

#Region "<< -Update- >>"
    Public Shared Sub Update(pCommission As CommissionCalculationBE)
        Dim wSqlConn As SqlConnection = Nothing
        Dim wSqlCmd As SqlCommand = Nothing
        Dim wConnStr As String = String.Empty

        Try
            'Obtengo la cadena de conexión
            wConnStr = ConfigurationManager.ConnectionStrings("connString").ConnectionString
            'Creo la conexión a la BD
            wSqlConn = New SqlConnection(wConnStr)
            'Creo el comando
            wSqlCmd = New SqlCommand()
            wSqlCmd.Connection = wSqlConn
            wSqlCmd.CommandType = CommandType.StoredProcedure
            wSqlCmd.CommandText = "[dbo].[CalculoComision_u]"

            wSqlCmd.Parameters.Add("@TipoInmueble", SqlDbType.Int).Value = pCommission.PropertyType.Id.Value
            wSqlCmd.Parameters.Add("@TipoOperacion", SqlDbType.Int).Value = pCommission.Operation.Id.Value
            wSqlCmd.Parameters.Add("@Monto", SqlDbType.Decimal).Value = pCommission.Mont.Value
            wSqlCmd.Parameters.Add("@EscalaValor", SqlDbType.Int).Value = pCommission.ValueScale.Value

            wSqlConn.Open()
            wSqlCmd.ExecuteNonQuery()
            wSqlConn.Close()
        Catch ex As Exception
            Throw ex
        End Try
    End Sub
#End Region

#Region "<< -Delete- >>"
    Public Shared Sub Delete(pCommission As CommissionCalculationBE)
        Dim wSqlConn As SqlConnection = Nothing
        Dim wSqlCmd As SqlCommand = Nothing
        Dim wConnStr As String = String.Empty

        Try
            'Obtengo la cadena de conexión
            wConnStr = ConfigurationManager.ConnectionStrings("connString").ConnectionString
            'Creo la conexión a la BD
            wSqlConn = New SqlConnection(wConnStr)
            'Creo el comando
            wSqlCmd = New SqlCommand()
            wSqlCmd.Connection = wSqlConn
            wSqlCmd.CommandType = CommandType.StoredProcedure
            wSqlCmd.CommandText = "[dbo].[CalculoComision_d]"

            wSqlCmd.Parameters.Add("@TipoInmueble", SqlDbType.Int).Value = pCommission.PropertyType.Id.Value
            wSqlCmd.Parameters.Add("@TipoOperacion", SqlDbType.Int).Value = pCommission.Operation.Id.Value

            wSqlConn.Open()
            wSqlCmd.ExecuteNonQuery()
            wSqlConn.Close()

        Catch ex As Exception
            Throw ex
        End Try
    End Sub
#End Region

End Class
